package com.elite.groovy.callback

import com.elite.groovy.kh.KHDBConfig
import com.elite.groovy.kh.KHUtil
import com.elite.groovy.util.EliteBean
import com.elite.groovy.util.Util
import net.sf.json.JSONArray
import net.sf.json.JSONObject
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.support.rowset.SqlRowSet

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import java.text.SimpleDateFormat

/**
 *
 * @Title: apiQWDeptUser 同步企微部门和人员
 *
 */

public class apiQWDeptUser implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiQWDeptUser.class);
    private static String update_Time = "";
    private static String QW_Token = "";

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");
        long startTime = System.currentTimeMillis();

        JSONObject outData = JSONObject.fromObject("{\"code\":1,\"message\":\"操作成功\"}");
        String getMethod = request.getMethod();
        log.debug("企微同步 getMethod:" + getMethod);

        // 获取所有Header
        Map<String, String> mapHeader = new HashMap<String, String>();
        Enumeration headerNames = request.getHeaderNames();
        while (headerNames.hasMoreElements()) {
            String key = (String) headerNames.nextElement();
            String value = request.getHeader(key);
            log.debug("企微同步 Header-" + key + ":" + value);

            mapHeader.put(key, value);
        }

        // 获取调用数据
        String optype = request.getParameter("optype");
        log.info("接收调用 参数信息：" + optype);

        // 获取数据源
        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());

        try {
            if (optype == "同步企微") {
                doQW_Main(eliteBean, jt);

            } else if (optype == "处理数据Rolegroup") {
                doQW_Rolegroup(eliteBean, jt);

            } else if (optype == "处理数据Staff") {
                doQW_Staff(eliteBean, jt);

            } else if (optype == "全量处理") {
                doQW_Main(eliteBean, jt);
                doQW_Rolegroup(eliteBean, jt);
                doQW_Staff(eliteBean, jt);

            } else {
                outData.put("code", -1);
                outData.put("message", "不支持的操作类型optype:" + optype);
            }

        } catch (Exception e) {
            outData.put("code", -2);
            outData.put("message", "HTTP请求调用错误(" + e.getMessage() + "),请稍后再试!");
            log.error(outData, e);
            return outData.toString();

        }
        log.debug("企微同步 返回:" + outData.toString());
        return outData.toString();

    }

    // 获取部门列表
    public static JSONObject doQW_Main(EliteBean eliteBean, JdbcTemplate jt) throws Exception {

        // 生成本次更新时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        update_Time = sdf.format(new Date());
        log.debug("企微同步- 生成本次更新时间 update_Time：" + update_Time);

        // 获取企微Token
        JSONObject apiTokenJson = JSONObject.fromObject(Util.doHttpGet(KHDBConfig.getAPIURL(eliteBean, "SYS-InnerDomain") + "groovy/GeneralService/apiGetToken?appid=WXG_DAVCO", "UTF-8", 12000, 0));
        log.debug("企微同步- 获取企微Token 调用返回：" + apiTokenJson.toString());

        // 解析企微Token
        QW_Token = apiTokenJson.getJSONObject("value").getString("access_token");
        log.debug("企微同步- 解析企微Token QW_Token：" + QW_Token);

        doQW_DeptList(eliteBean, jt);

    }

    // 获取部门列表
    public static JSONObject doQW_DeptList(EliteBean eliteBean, JdbcTemplate jt) throws Exception {

        // 获取企微Token
        String api_URL = "https://qyapi.weixin.qq.com/cgi-bin/department/list?access_token=" + QW_Token;
        log.debug("企微同步- 获取部门列表 URL：" + api_URL);

        // 获取调用URL
        String api_Res = Util.doHttpGet(api_URL, "UTF-8", 120000, 0);
        log.debug("企微同步- 获取部门列表 Res：" + api_Res);

        // 解析
        JSONObject api_Json = JSONObject.fromObject(api_Res);
        JSONArray api_List = api_Json.getJSONArray("department");

        // 初始化数据
        jt.update("Update Sync_Qw_Dept Set Update_Flag = -1");
        // 初始化数据
        jt.update("Update Sync_Qw_User Set Update_Flag = -1");

        // 初始化补全上级
        jt.update("Replace Into Sync_Qw_Dept (Id, Name, Name_En, Department_Leader, Parentid, d_Order, Update_Time, Update_Flag) Values (1, '西卡BFM', '', '[]', NULL, 1, '2024-04-23 18:13:12', 1)");
        jt.update("Replace Into Sync_Qw_Dept (Id, Name, Name_En, Department_Leader, Parentid, d_Order, Update_Time, Update_Flag) Values (2, '西卡建筑装修材料 · 中国', '', '[]', 1, 2, '2024-04-23 18:13:12', 1)");

        for (int i = 0; i < api_List.size(); i++) {

            String id = KHUtil.GetSFJV(api_List.getJSONObject(i), "id");
            String name = KHUtil.GetSFJV(api_List.getJSONObject(i), "name");
            String name_en = KHUtil.GetSFJV(api_List.getJSONObject(i), "name_en");
            String department_leader = KHUtil.GetSFJV(api_List.getJSONObject(i), "department_leader");
            String parentid = KHUtil.GetSFJV(api_List.getJSONObject(i), "parentid");
            String d_order = KHUtil.GetSFJV(api_List.getJSONObject(i), "order");

            // 数据入库
            jt.update("Replace Into Sync_Qw_Dept (Id, Name, Name_En, Department_Leader, Parentid, d_Order, Update_Time, Update_Flag) Values (?, ?, ?, ?, ?, ?, ?, 1)", id, name, name_en, department_leader, parentid, d_order, update_Time);

            // 获取部门成员详情
            doQW_DeptUser(jt, id);
        }

        return api_Json;

    }

    // 获取部门成员
    public static JSONObject doQW_DeptUser(JdbcTemplate jt, String Dept_ID) throws Exception {

        // 获取企微Token
        String api_URL = "https://qyapi.weixin.qq.com/cgi-bin/user/list?access_token=" + QW_Token + "&department_id=" + Dept_ID;
        log.debug("企微同步- 获取部门成员 URL：" + api_URL);

        // 获取调用URL
        String api_Res = Util.doHttpGet(api_URL, "UTF-8", 120000, 0);
        log.debug("企微同步- 获取部门成员 Res：" + api_Res);

        // 解析
        JSONObject api_Json = JSONObject.fromObject(api_Res);
        JSONArray api_List = api_Json.getJSONArray("userlist");

        for (int i = 0; i < api_List.size(); i++) {

            String userid = KHUtil.GetSFJV(api_List.getJSONObject(i), "userid");
            String name = KHUtil.GetSFJV(api_List.getJSONObject(i), "name");
            String english_name = KHUtil.GetSFJV(api_List.getJSONObject(i), "english_name");
            String mobile = KHUtil.GetSFJV(api_List.getJSONObject(i), "mobile");
            String hide_mobile = KHUtil.GetSFJV(api_List.getJSONObject(i), "hide_mobile");
            String department = KHUtil.GetSFJV(api_List.getJSONObject(i), "department");
            String d_order = KHUtil.GetSFJV(api_List.getJSONObject(i), "order");
            String position = KHUtil.GetSFJV(api_List.getJSONObject(i), "position");
            String gender = KHUtil.GetSFJV(api_List.getJSONObject(i), "gender");
            String email = KHUtil.GetSFJV(api_List.getJSONObject(i), "email");
            String biz_mail = KHUtil.GetSFJV(api_List.getJSONObject(i), "biz_mail");
            String is_leader_in_dept = KHUtil.GetSFJV(api_List.getJSONObject(i), "is_leader_in_dept");
            String isleader = KHUtil.GetSFJV(api_List.getJSONObject(i), "isleader");
            String direct_leader = KHUtil.GetSFJV(api_List.getJSONObject(i), "direct_leader");
            String avatar = KHUtil.GetSFJV(api_List.getJSONObject(i), "avatar");
            String thumb_avatar = KHUtil.GetSFJV(api_List.getJSONObject(i), "thumb_avatar");
            String telephone = KHUtil.GetSFJV(api_List.getJSONObject(i), "telephone");
            String alias = KHUtil.GetSFJV(api_List.getJSONObject(i), "alias");
            String extattr = KHUtil.GetSFJV(api_List.getJSONObject(i), "extattr");
            String status = KHUtil.GetSFJV(api_List.getJSONObject(i), "status");
            String qr_code = KHUtil.GetSFJV(api_List.getJSONObject(i), "qr_code");
            String external_profile = KHUtil.GetSFJV(api_List.getJSONObject(i), "external_profile");
            String external_position = KHUtil.GetSFJV(api_List.getJSONObject(i), "external_position");
            String address = KHUtil.GetSFJV(api_List.getJSONObject(i), "address");
            String open_userid = KHUtil.GetSFJV(api_List.getJSONObject(i), "open_userid");
            String main_department = KHUtil.GetSFJV(api_List.getJSONObject(i), "main_department");
            String enable = KHUtil.GetSFJV(api_List.getJSONObject(i), "enable");

            // 数据入库
            jt.update("Replace Into Sync_Qw_User (Userid, Name, English_Name, Mobile, Hide_Mobile, Department, d_Order, Position, Gender, Email, Biz_Mail, Is_Leader_In_Dept, Isleader, Direct_Leader, Avatar, Thumb_Avatar, Telephone, Alias, Extattr, Status, Qr_Code, External_Profile, External_Position, Address, Open_Userid, Main_Department, Enable, Update_Time, Update_Flag) Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)", userid, name, english_name, mobile, hide_mobile, department, d_order, position, gender, email, biz_mail, is_leader_in_dept, isleader, direct_leader, avatar, thumb_avatar, telephone, alias, extattr, status, qr_code, external_profile, external_position, address, open_userid, main_department, enable, update_Time);

            doQW_DeptUser_Attrs(jt, userid, extattr);

        }

        return api_Json;

    }

    // 处理员工拓展信息
    public static JSONObject doQW_DeptUser_Attrs(JdbcTemplate jt, String userid, String extattr) throws Exception {

        JSONObject api_Json = new JSONObject();
        try {
            // 解析
            api_Json = JSONObject.fromObject(extattr);
            JSONArray api_List = api_Json.getJSONArray("attrs");

            // 初始化数据
            jt.update("Update Sync_Qw_User_Attrs Set Update_Flag = -1 Where userid = ?", userid);

            for (int i = 0; i < api_List.size(); i++) {

                String name = KHUtil.GetSFJV(api_List.getJSONObject(i), "name");
                String value = KHUtil.GetSFJV(api_List.getJSONObject(i), "value");

                // 数据入库
                jt.update("Replace Into Sync_Qw_User_Attrs (Userid, Name, Value, Update_Time, Update_Flag) Values (?, ?, ?, ?, 1)", userid, name, value, update_Time);

            }
        } catch (Exception e) {
            log.error("处理客服系统 Rolegroup 异常：" + e.getMessage());
        }

        return api_Json;

    }

    // 处理客服系统 Rolegroup
    public static JSONObject doQW_Rolegroup(EliteBean eliteBean, JdbcTemplate jt) {

        try {
            long starttime = System.currentTimeMillis();
            //获取数据
            SqlRowSet SqlRow_Role = jt.queryForRowSet("Select d.Id, d.Name, d.Parentid, d.Update_Time From Sync_Qw_Dept d");

            //遍历数据
            while (SqlRow_Role.next()) {
                String qw_id = SqlRow_Role.getString("Id");//企微组别id
                String qw_name = SqlRow_Role.getString("Name");//企微组别名字
                String qw_parentid = SqlRow_Role.getString("Parentid");//企微父级ID
                log.debug("处理客服系统 Rolegroup qw_id：" + qw_id + " qw_name：" + qw_name + " qw_parentid：" + qw_parentid);

                String rolegroup_id = "";//定义组别变量
                String parent_id = "";//定义父级变量

                // 查询当前取到的父级组是否在系统中存在,不存在则新建一个(父级id)
                if (!KHUtil.isNull(qw_parentid)) {
                    String checkParSQL = '''Select Rolegroup_Id From Rolegroup Where Qw_Rolegroup_Id = ?''';
                    List<Map<String, Object>> checkParList = jt.queryForList(checkParSQL, qw_parentid);
                    if (checkParList.size() > 0) {
                        parent_id = checkParList.get(0).get("Rolegroup_Id");
                    } else {
                        //开始新增父级组
                        parent_id = KHUtil.getRandomString(6).toUpperCase(); //生成六位随机id
                        String updateSQL = "Insert Into Rolegroup (Rolegroup_Id, Qw_Rolegroup_Id, Status, Createddate, Createdby, Source) Values ('" + parent_id + "', '" + qw_parentid + "', 1, Now(), '000023', '1')";
                        jt.update(updateSQL);

                        //插入表mprojectrolegroup话务员组和项目对应表
                        updateSQL = "Insert Into Mprojectrolegroup (Rolegroup_Id, Mproject_Id, Mprojectrolegroup_Id) Values ('" + parent_id + "', 'MMYSQL', Uuid())";
                        jt.update(updateSQL);

                    }
                }

                // 查询当前取到的企微组id是否在系统存在（组id）
                String checkRolSQL = '''Select Qw_Rolegroup_Id, Rolegroup_Id From Rolegroup Where Qw_Rolegroup_Id = ?''';
                List<Map<String, Object>> checkRolList = jt.queryForList(checkRolSQL, qw_id);
                if (checkRolList.size() > 0) {
                    rolegroup_id = checkRolList.get(0).get("Rolegroup_Id");
                    // 存在修改
                    String updateSQL = '''Update Rolegroup
                                           Set Rolegroupdescription = ?, Parent_Rolegroupid = ?, Modifieddate = Now(), Modifiedby = '00023', Source = '1', Status = 1
                                         Where Rolegroup_Id = ?''';
                    jt.update(updateSQL, qw_name, parent_id, rolegroup_id);

                    // 先删后增
                    updateSQL = "Delete From Authority_Relationship Where Lower = '" + rolegroup_id + "' And Higherflag = '0' And Lowerflag = '0'";
                    jt.update(updateSQL);

                    if (KHUtil.isNull(parent_id)) {
                        updateSQL = "Insert Into Authority_Relationship (Authority_Id, Higher, Higherflag, Lower, Lowerflag) Values (Uuid(), null, 0, ?, 0)";
                        jt.update(updateSQL, rolegroup_id);
                    } else {
                        updateSQL = "Insert Into Authority_Relationship (Authority_Id, Higher, Higherflag, Lower, Lowerflag) Values (Uuid(), ?, 0, ?, 0)";
                        jt.update(updateSQL, parent_id, rolegroup_id);
                    }

                    log.debug("处理客服系统 Rolegroup 存在修改 先删后增...");

                } else {
                    //在系统表里找不到 组别--->开始新增组别
                    String ram_id = KHUtil.getRandomString(6).toUpperCase();//随机id
                    String updateSQL = "Insert Into Rolegroup (Rolegroup_Id, Qw_Rolegroup_Id, Rolegroupdescription, Status, Parent_Rolegroupid, Createddate, Createdby, Source) Values ('" + ram_id + "', '" + qw_id + "', '" + qw_name + "', '1', '" + parent_id + "', Now(), '000023', '1')";
                    jt.update(updateSQL);

                    //插入表mprojectrolegroup话务员组和项目对应表
                    updateSQL = "Insert Into Mprojectrolegroup (Rolegroup_Id, Mproject_Id, Mprojectrolegroup_Id) Values ('" + ram_id + "', 'MMYSQL', Uuid())";
                    jt.update(updateSQL);

                    if (!KHUtil.isNull(parent_id)) {
                        //获取父级关系--入层级表
                        updateSQL = "Insert Into Authority_Relationship (Authority_Id, Higher, Higherflag, Lower, Lowerflag) Values (Uuid(), '" + parent_id + "', 0, '" + ram_id + "', 0)";
                        jt.update(updateSQL);

                    }
                    log.debug("处理客服系统 Rolegroup 不存在新增 ...");

                }

            }

            // 处理未同步到的组
            int no_Num = jt.update("Update Rolegroup Set Status = 0 Where Source = 1 And Qw_Rolegroup_Id Not In (Select Id From Sync_Qw_Dept Where Update_Flag = 1)");
            int del_Hig = jt.update("Delete From Authority_Relationship Where Higher In (Select Rolegroup_Id From Rolegroup Where Source = 1 And Status = 0) And Higherflag = 0");
            int del_Low = jt.update("Delete From Authority_Relationship Where Lower In (Select Rolegroup_Id From Rolegroup Where Source = 1 And Status = 0) And Lowerflag = 0");
            log.debug("处理客服系统 Rolegroup 处理未同步到的组 关闭数量:" + no_Num + " 删除上级:" + del_Hig + " 删除下级:" + del_Low);

            long endtime = System.currentTimeMillis();
            log.debug("处理客服系统 Rolegroup 执行耗时:" + (endtime - starttime));

        } catch (Exception e) {
            log.error("处理客服系统 Rolegroup 异常：" + e);
        }

    }

    // 处理客服系统 Staff
    public static JSONObject doQW_Staff(EliteBean eliteBean, JdbcTemplate jt) {

        try {
            long starttime = System.currentTimeMillis();

            //获取企微人员数据
            String sync_GetSQL = '''Select Distinct Ifnull(R2.Value, R1.Userid) As Id,
                                                R1.Userid,
                                                R1.Name,
                                                R1.Mobile,
                                                R1.Main_Department As Department,
                                                R1.Position,
                                                R1.Gender,
                                                R1.Email,
                                                R1.Biz_Mail,
                                                R1.Is_Leader_In_Dept,
                                                R1.Status,
                                                R1.Main_Department,
                                                R1.Update_Time
                                  From Sync_Qw_User R1
                                  Left Join Sync_Qw_User_Attrs R2
                                    On R1.Userid = R2.Userid
                                   And R2.Name = '员工编号\'''';
            SqlRowSet SqlRow_User = jt.queryForRowSet(sync_GetSQL);

            //循环遍历
            while (SqlRow_User.next()) {
                String id = SqlRow_User.getString("Id");//员工编号
                String userid = SqlRow_User.getString("Userid");//成员USERID
                String name = SqlRow_User.getString("Name");//成员名称
                String mobile = SqlRow_User.getString("Mobile");//手机号码
                String department = SqlRow_User.getString("Department");//成员所属部门
                String position = SqlRow_User.getString("Position");//职务信息
                String gender = SqlRow_User.getString("Gender");//性别
                String email = SqlRow_User.getString("Email");//邮箱
                String biz_mail = SqlRow_User.getString("Biz_Mail");//企业邮箱
                String is_leader_in_dept = SqlRow_User.getString("Is_Leader_In_Dept");//表示在所在的部门内是否为部门负责人。0-否 1 是
                String status = SqlRow_User.getString("Status");//激活状态 1 已激活  2 已禁用  4 为激活   5退出企业
                String main_department = SqlRow_User.getString("Main_Department");//主部门
                String updated_at = SqlRow_User.getString("Update_Time");//企微人员修改时间
                log.debug("处理客服系统 Staff userid：" + userid + " name:" + name);

                String workstatus = null;
                String ISSUPERVISOR = null;
                String rolegroup_id = null;
                String staff_id = null;
                String ram_id = KHUtil.getRandomString(6).toUpperCase();//随机id

                //翻译------如果激活状态是5 那么就标记成离职
                if (status.equals("5")) {
                    workstatus = "QUIT";
                    name = name + "-停用";
                } else {
                    workstatus = "ONLINE";
                }
                //翻译------如果是部门负责人将设置成5
                if (is_leader_in_dept.equals("1")) {
                    ISSUPERVISOR = 5;
                } else {
                    ISSUPERVISOR = 1;
                }

                // 判断人员是否已经存在
                String selectSql = '''Select Staff_Id From Staff Where Userid = ? Limit 1''';
                List<Map<String, Object>> selelctList = jt.queryForList(selectSql, userid);
                if (selelctList.size() > 0) {
                    staff_id = selelctList.get(0).get("Staff_Id");
                }

                //判断部门是否存在
                String selectSql3 = "Select Rolegroup_Id From Rolegroup Where Qw_Rolegroup_Id = '" + department + "' Limit 1";
                List<Map<String, Object>> sqlResult3 = jt.queryForList(selectSql3);
                log.debug("处理客服系统 Staff 判断部门是否存在：" + sqlResult3);
                if (sqlResult3.size() > 0) {
                    rolegroup_id = sqlResult3.get(0).get("Rolegroup_Id");
                } else {
                    log.debug("处理客服系统 Staff 系统找不到该组：" + rolegroup_id);

                }

                if (selelctList.size() > 0) {
                    log.debug("处理客服系统 Staff 已经存在staff表：" + staff_id);
                    //判断已经存在 ----执行修改
                    String updatesql = '''Update Staff
                                           Set Staffname         = ?,
                                               Workstatus        = ?,
                                               Issupervisor      = ?,
                                               Loginname         = ?,
                                               Defaultrolegroup  = ?,
                                               e_Mail            = ?,
                                               Gender            = ?,
                                               Mobile            = ?,
                                               Biz_Mail          = ?,
                                               Userid            = ?,
                                               Department        = ?,
                                               Position          = ?,
                                               Is_Leader_In_Dept = ?,
                                               Status            = ?,
                                               Qw_Id             = ?,
                                               Main_Department   = ?,
                                               Qw_Sync_Time      = Now(),
                                               Modifiedby        = '000023',
                                               Modifieddate      = Now(),
                                               Source            = '1'
                                         Where Staff_Id = ?''';

                    jt.update(updatesql, name, workstatus, ISSUPERVISOR, userid, rolegroup_id, email, gender, mobile, biz_mail, userid, department, position, is_leader_in_dept, status, id, main_department, staff_id);

                    String updateSql3 = "Delete From Staffrolegroup Where Staff_Id = '" + staff_id + "' and Staff_Id not in (select staff_id from staff where device_id = '禁企微自动更新人组') And Rolegroup_Id != 'SYSTEM'";
                    String dataresult4 = jt.update(updateSql3);
                    log.debug("处理客服系统 Staff 删除staffrolegroup数据结果:" + dataresult4);

                    String updateSql2 = "Replace Into Staffrolegroup (Staff_Id, Rolegroup_Id) Values ('" + staff_id + "', '" + rolegroup_id + "')";
                    String dataresult5 = jt.update(updateSql2);
                    log.debug("处理客服系统 Staff 新增staffrolegroup数据结果:" + dataresult5);

                } else {
                    //查询不到执行新增staff
                    log.debug("处理客服系统 Staff 查询不到执行新增staff：" + staff_id);
                    String insertsql = '''Insert Into Staff
                                              (Staff_Id,
                                               Staffname,
                                               Workstatus,
                                               Issupervisor,
                                               Loginname,
                                               Defaultrolegroup,
                                               e_Mail,
                                               Gender,
                                               Mobile,
                                               Biz_Mail,
                                               Userid,
                                               Department,
                                               Position,
                                               Is_Leader_In_Dept,
                                               Status,
                                               Qw_Id,
                                               Main_Department,
                                               Qw_Sync_Time,
                                               Createdby,
                                               Createddate,
                                               Source)
                                            Values
                                              (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, Now(), '000023', Now(), '1')''';
                    jt.update(insertsql, ram_id, name, workstatus, ISSUPERVISOR, userid, rolegroup_id, email, gender, mobile, biz_mail, userid, department, position, is_leader_in_dept, status, id, main_department);

                    String insertSql2 = "Replace Into Staffrolegroup (Staff_Id, Rolegroup_Id) Values ('" + ram_id + "', '" + rolegroup_id + "')";
                    String dataresult2 = jt.update(insertSql2);
                    log.debug("处理客服系统 Staff 新增staffrolegroup数据结果:" + dataresult2);

                }

                // 补充用户手机号(部分企微用户手机号字段没有值,从拓展信息表再取一次)
                String updateSQLa = '''Update Staff R1,
                                       (Select R1.Staff_Id, R1.Staffname, R1.Loginname, R2.Userid, R2.Department, R2.Position, R2.Thumb_Avatar, R3.Value
                                          From Staff R1
                                          Left Join Sync_Qw_User R2
                                            On R1.Userid = R2.Userid
                                          Left Join Sync_Qw_User_Attrs R3
                                            On R2.Userid = R3.Userid
                                           And R3.Name = 'Mobile'
                                         Where R1.Source = '1'
                                           And (R1.Mobile Is Null Or R1.Mobile = '')
                                           And R3.Userid Is Not Null) R2
                                   Set R1.Mobile = R2.Value
                                 Where R1.Staff_Id = R2.Staff_Id
                                   And R1.Userid = ?''';
                int updateSQLa_Res = jt.update(updateSQLa, userid);
                log.debug("补充用户手机号(部分企微用户手机号字段没有值,从拓展信息表再取一次) 结果:" + updateSQLa_Res);

            }

            // 处理未同步到的人员
            int no_Num = jt.update("Update Staff Set Workstatus = 'QUIT' Where Source = 1 And Userid Not In (Select Userid From Sync_Qw_User Where Update_Flag = 1)");
            int del_Str = jt.update("Delete From Staffrolegroup Where Staff_Id In (Select Staff_Id From Staff Where Source = 1 And Workstatus = 'QUIT')");
            log.debug("处理客服系统 Rolegroup 处理未同步到的人员 关闭数量:" + no_Num + " 删除人&组:" + del_Str);

            long endtime = System.currentTimeMillis();
            log.debug("处理客服系统 Staff 执行耗时:" + (endtime - starttime));

        } catch (Exception e) {
            log.error("处理客服系统 Staff 异常：" + e);
        }

    }

}